﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using Common;

using System.IO;
using System.Data.OleDb;

namespace DAL
{
    public class Contract
    {
        public Dat_Contract SelectContract(int ContractKey)
        {
            CIMDataContext db = new CIMDataContext();

            return db.Dat_Contract.Single(c => c.ContractKey == ContractKey);
        }

        public Dat_Contract SelectContract(string ContractNO)
        {
            CIMDataContext db = new CIMDataContext();

            return db.Dat_Contract.Single(c => c.ContractNO == ContractNO);
        }

        //添加合同
        public bool AddContract(Dat_Contract contract)
        {
            bool Success;
            CIMDataContext db = new CIMDataContext();

            db.Dat_Contract.InsertOnSubmit(contract);
            try
            {
                db.SubmitChanges();
                Success = true;
            }
            catch (Exception ex)
            {
                Success = false;
            }
            return Success;
        }

        //删除合同
        public void DelContract(int ContractKey)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Contract contract = db.Dat_Contract.Single(c => c.ContractKey == ContractKey);
            db.Dat_Contract.DeleteOnSubmit(contract);
            db.SubmitChanges();
        }

        //修改合同
        public bool EditContract(Dat_Contract contract)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Contract Contract = db.Dat_Contract.Single(c => c.ContractKey == contract.ContractKey);
            
            Contract.MUserKey = contract.MUserKey;
            Contract.ContractType = contract.ContractType;
            Contract.ContractNO = contract.ContractNO;
            Contract.StartDate = contract.StartDate;
            Contract.EndDate = contract.EndDate;
            Contract.ContractPrice = contract.ContractPrice;
            Contract.PayPeriod = contract.PayPeriod;
            Contract.Remark = contract.Remark;
         
            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //条件查询
        public IQueryable<Dat_Contract> SelectContract(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_Contract> Query;

            if (QueryTable == null)
            {
                Query = from c in db.Dat_Contract
                        orderby c.ContractKey descending
                        select c;
            }
            else
            {
                Query = from c in GetConditionQuery(QueryTable)
                        orderby c.ContractKey descending
                        select c;
            }
            return Query;
        }

        public List<Dat_Contract> SelectContract(Hashtable QueryTable,int CurPage,int PageSize)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_Contract> Query;

            if (QueryTable == null)
            {
                Query = from c in db.Dat_Contract
                        orderby c.ContractKey descending
                        select c;
            }
            else
            {
                Query = from c in GetConditionQuery(QueryTable)
                        orderby c.ContractKey descending
                        select c;
            }
            return Query.Skip((CurPage - 1) * PageSize).Take(PageSize).ToList();
        }

        //快到期合同数
        public int GetExpireCount(int days)
        {   
            int count = 0;

            CIMDataContext db = new CIMDataContext();
            
            int UserType = Convert.ToInt32(HttpContext.Current.Session["UserType"]);
            if ( UserType == 4 )  //管理员
            {
                 count = (   from c in db.Dat_Contract
                             where c.EndDate != null && 
                             (c.EndDate - System.DateTime.Now.Date).Days <= days &&
                             (c.EndDate - System.DateTime.Now.Date).Days >= 0
                             select c).Count();
            }
            if ( UserType == 1 )   //客户
            {
                count = (   from c in db.Dat_Contract
                            where c.MUserKey == Convert.ToInt32(HttpContext.Current.Session["UserKey"]) &&
                                  c.EndDate != null &&
                                  (c.EndDate - System.DateTime.Now.Date).Days <= days &&
                                  (c.EndDate - System.DateTime.Now.Date).Days >= 0
                             select c ).Count();
            }
            return count;
        }

        public int GetTotalCount(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_Contract> Query;

            if (QueryTable == null)
            {
                Query = from c in db.Dat_Contract
                        orderby c.ContractKey descending
                        select c;
            }
            else
            {
                Query = from c in GetConditionQuery(QueryTable)
                        orderby c.ContractKey descending
                        select c;
            }
            return Query.Count();
        }

        private IQueryable<Dat_Contract> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            var predicate = PredicateBuilder.True<Dat_Contract>();

            if (QueryTable["CustName"] != null)
                predicate = predicate.And(i => 1==1);
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(i => 1 == 1);
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(c => c.StartDate >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(c => c.EndDate <= TwoDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["MenuType"] != null)
            {
                int UserType = Convert.ToInt32(HttpContext.Current.Session["UserType"]);
                int UserKey = Convert.ToInt32(HttpContext.Current.Session["UserKey"]);

                if (UserType == 1)  //客户显示自己记录，其余显示全部记录
                {
                    predicate = predicate.And(n => n.MUserKey == UserKey);
                }
            }
            if (QueryTable["ExpireDay"] != null)
            {
                int UserType = Convert.ToInt32(HttpContext.Current.Session["UserType"]);
                int UserKey = Convert.ToInt32(HttpContext.Current.Session["UserKey"]);

                if (UserType == 4)  //管理员
                {
                    predicate = predicate.And(n => (n.EndDate - System.DateTime.Now.Date).Days <= Convert.ToInt32(QueryTable["ExpireDay"]) && (n.EndDate - System.DateTime.Now.Date).Days >=0 );
                }
                if (UserType == 1)  //客户
                {
                    predicate = predicate.And(n => (n.EndDate - System.DateTime.Now.Date).Days <= Convert.ToInt32(QueryTable["ExpireDay"]) && (n.EndDate - System.DateTime.Now.Date).Days >= 0 );
                    predicate = predicate.And(n => n.MUserKey == UserKey );
                }
            }

            return db.Dat_Contract.Where(predicate);
        }

        //Export
        public static void DataToExcel(Hashtable QueryTable)
        {
            IQueryable<Dat_Contract> Query = new Contract().SelectContract(QueryTable);

            WebUtility.DelExport();

            string FileName = "~/export/" + Guid.NewGuid().ToString() + ".xls";
            string sNewFullFile = HttpContext.Current.Server.MapPath(FileName);

            try
            {
                File.Copy(HttpContext.Current.Server.MapPath("~/common/template.xls"), sNewFullFile);
            }
            catch (Exception er)
            {
                HttpContext.Current.Response.Write(er.Message);
                return;
            }

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = null;

            bool bRet = false;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [合同信息]([客户名称] Text,[合同类型] Text,[起止日期] Text,[合同金额] Text,[付款周期] Text)", conn);
                cmd.ExecuteNonQuery();


                string strSQL = "INSERT INTO [合同信息$] ([客户名称], [合同类型],[起止日期],[合同金额],[付款周期]) VALUES (?,?,?,?,?)";
                cmd = new OleDbCommand(strSQL, conn);
                for (int i = 0; i < 5; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                foreach (Dat_Contract contract in Query)
                {
                    cmd.Parameters[0].Value = contract.MUserKey == null ? "" : new Account().GetAccountAllName(contract.MUserKey);
                    cmd.Parameters[1].Value = string.IsNullOrEmpty(contract.ContractType) ? "" : contract.ContractType;
                    cmd.Parameters[2].Value = WebUtility.DateTimeToShortString(contract.StartDate) + "至" + WebUtility.DateTimeToShortString(contract.EndDate);
                    cmd.Parameters[3].Value = contract.ContractPrice == null ? "" : WebUtility.DecimalToString(contract.ContractPrice);
                    cmd.Parameters[4].Value = string.IsNullOrEmpty(contract.PayPeriod) ? "" : contract.PayPeriod;

                    cmd.ExecuteNonQuery();
                }
                bRet = true;
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);

            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Dispose();
            }

            if (bRet)
                HttpContext.Current.Response.Redirect(FileName);
        }
    }
}
